#!/usr/local/bin/python
# _*_ coding: utf-8 _*_

import pymysql
from scrapy.utils.project import get_project_settings

class DBHelper():
    ''' 封装MySQL操作类 '''

    def __init__(self):
        self.settings = get_project_settings()

        self.host   = self.settings['MYSQL_HOST']
        self.port   = self.settings['MYSQL_PORT']
        self.user   = self.settings['MYSQL_USER']
        self.passwd = self.settings['MYSQL_PASSWD']
        self.db     = self.settings['MYSQL_DBNAME']

    # 连接到MySQL，连接到settings配置的数据库
    def connectDatabase(self):
        conn = pymysql.connect(self.host, self.user, self.passwd, self.db)
        return conn

    # 执行SQL语句
    # 注：params参数要加*，因为传递过来的是元组，*表示参数个数不定
    def execute(self, sql, *params):
        conn = self.connectDatabase()
        cursor = conn.cursor()
        try:
            cursor.executemany(sql, *params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            return e
        cursor.close()
        conn.close()

        return cursor.fetchall()

    # 插入数据
    def insert(self, sql, *params):
        conn = self.connectDatabase()
        cursor = conn.cursor()
        try:
            cursor.executemany(sql, *params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            return e
        cursor.close()
        conn.close()
        # 第一条插入id
        new_id = cursor.lastrowid
        # 获取执行条目数
        rows = cursor.rowcount
        # 最后id为 new_id + rows -1

        return {'id': new_id, 'rows': rows}

    # 更新数据
    def update(self, sql, *params):
        conn = self.connectDatabase()
        cursor = conn.cursor()
        try:
            cursor.executemany(sql, *params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            return e
        cursor.close()
        conn.close()

        # 返回执行条目数
        return cursor.rowcount

    # 查询数据
    def select(self, sql, *params):
        conn = self.connectDatabase()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, *params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            return e
        cursor.close()
        conn.close()

        return cursor.fetchall()

    # 删除数据
    def delete(self, sql, *params):
        conn = self.connectDatabase()
        cursor = conn.cursor()
        try:
            cursor.execute(sql, *params)
            conn.commit()
        except Exception as e:
            conn.rollback()
            return e
        cursor.close()
        conn.close()

        # 获取执行条目数
        rows = cursor.rowcount

        return rows

# 测试DBHelper类
class TestDBHelper():
    ''' DBHelper 测试类 '''

    def __init__(self):
        self.dbhelper = DBHelper()

    # 测试执行SQL语句
    def testExecute(self):
        # sql = "SHOW TABLES"
        sql = 'INSERT INTO proxies (id, ip, port, type, position, speed, last_check_time) VALUES (%s, %s, %s, %s, %s, %s, %s)'

        item = {
            'ip': '192.168.1.1',
            'port': '8833',
            'type': 'https',
            'position': '北京',
            'speed': '0.32',
            'last_check_time': '1922-07-14 09:23:22'}

        params = (0, str(item['ip']), str(item['port']), str(item['type']), str(item['position']), str(item['speed']), str(item['last_check_time']))
        results = self.dbhelper.execute(sql, params)
        print(results)

    def testInsert(self):
        sql = 'INSERT INTO proxies (id, ip, port, type, position, speed, \
        last_check_time) \
         VALUES (%s, %s, %s, %s, %s, %s, %s)'
        item = {
            'ip': '192.168.1.1',
            'port': '8833',
            'type': 'https',
            'position': '北京',
            'speed': '0.32',
            'last_check_time': '1922-07-14 09:23:22'}
        # 执行单条
        params = (
            (0, str(item['ip']), str(item['port']), str(item['type']),
                str(item['position']), str(item['speed']),
                str(item['last_check_time'])),
        )
        # 执行多条
        params = (
            (0, str(item['ip']), str(item['port']), str(item['type']),
                str(item['position']), str(item['speed']),
                str(item['last_check_time'])),
            (0, str(item['ip']), str(item['port']), str(item['type']),
                str(item['position']), str(item['speed']),
                str(item['last_check_time']))
        )
        results = self.dbhelper.insert(sql, params)
        print(results)

    def testUpdate(self):
        sql = 'UPDATE proxies SET ip = %s, type = %s WHERE id = %s'
        # 执行单条
        params = (
            (str('192.168.1.3'), str('httpp'), str('1')),
        )
        # 执行多条
        params = (
            (str('192.168.1.2'), str('httpp'), str('1')),
            (str('192.168.1.2'), str('httpp'), str('2'))
        )
        results = self.dbhelper.update(sql, params)
        print(results)

    def testSelect(self):
        sql = 'SELECT * FROM proxies WHERE id = %s'
        params = ('1')
        results = self.dbhelper.select(sql, params)
        print(results)

    def testDelete(self):
        sql = 'DELETE FROM proxies WHERE id = %s'
        params = ('2')
        results = self.dbhelper.delete(sql, params)
        print(results)

if __name__ == '__main__':
    testDBHelper = TestDBHelper()
    # testDBHelper.testInsert()
    # testDBHelper.testUpdate()
    # testDBHelper.testSelect()
    # testDBHelper.testDelete()
